Assume you are given the table below containing
information on user transactions for particular products. Write a query to
obtain the year-on-year growth rate for the total spend of each product for
each year.
Output the year (in ascending order) partitioned
by product id, current year's spend, previous year's spend and year-on-year
growth rate (percentage rounded to 2 decimal places).
table: user_transacions
Solution:
with cte as
(
select year(transaction_date) as yr,product_id,
spend as curr_year_spend,
lag(spend,1) over (partition by product_id order by year(transaction_date)) as
prev_year_spend
from user_transactions
)
select * , convert(decimal(18,2),(curr_year_spend -
prev_year_spend)/prev_year_spend
* 100.0) as yoy_rate from cte
Output:
SQL Script:
CREATE TABLE [dbo].[user_transactions](
[transaction_id]
[int] NULL,
[product_id]
[int] NULL,
[spend]
[decimal](18, 2) NULL,
[transaction_date]
[datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[user_transactions] ([transaction_id], [product_id], [spend],
[transaction_date]) VALUES (1341, 123424, CAST(1500.60 AS Decimal(18, 2)), CAST(N'2019-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[user_transactions] ([transaction_id], [product_id], [spend],
[transaction_date]) VALUES (1423, 123424, CAST(1000.20 AS Decimal(18, 2)), CAST(N'2020-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[user_transactions] ([transaction_id], [product_id], [spend],
[transaction_date]) VALUES (1623, 123424, CAST(1246.44 AS Decimal(18, 2)), CAST(N'2021-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[user_transactions] ([transaction_id], [product_id], [spend],
[transaction_date]) VALUES (1322, 123424, CAST(2145.32 AS Decimal(18, 2)), CAST(N'2022-12-31T00:00:00.000' AS DateTime))
GO